#!/usr/bin/env python
# -*- encoding: utf-8 -*-

import pandas as pd

from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.dp_common_job import DP011MJob__df7


class DP011_N_Job(AbstractDPJob):
    """
    获取消耗信息 按照类型调用不同存储过程

    SU_AJBG_DP0102 主原料
    BPC 能耗
    BPC 锌耗

    从main函数传入参数
    p_account,p_cost_center,p_unit,p_account_period_start,p_account_period_end,p_data_type
    """

    cost_center_org = None
    data_type = None

    # NOTE p_wce_org =v_wce,
    # NOTE p_wce=LEFT(v_wce,1)||'@@@@'
    wce_org = None
    wce = None

    cal_type = None
    coef = None
    mon = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center_org=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None,
                 p_wce_org=None,
                 p_wce=None,
                 p_cal_type=None,
                 p_coef=None,
                 p_mon=None,p_df7=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center_org:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        :param p_wce_org: 成本科目编号
        :param p_wce:
        :param p_cal_type:
        :param p_coef:
        :param p_mon:
        """
        super(DP011_N_Job, self).__init__(p_config=p_config,
                                          p_db_conn_mpp=p_db_conn_mpp,
                                          p_db_conn_rds=p_db_conn_rds,
                                          p_db_conn_dbprod7=p_db_conn_dbprod7,
                                          p_unit=p_unit,
                                          p_account=p_account,
                                          p_cost_center=p_cost_center,
                                          p_account_period_start=p_account_period_start,
                                          p_account_period_end=p_account_period_end)
        self.cost_center_org = p_cost_center_org
        self.data_type = p_data_type
        self.wce_org = p_wce_org
        self.wce = p_wce
        self.cal_type = p_cal_type
        self.coef = p_coef
        self.mon = p_mon
        self.df7 = p_df7

    def do_execute(self):
        """
        """
        self.logger.info('DP011_L_Job.do_execute')
        if self.cal_type == 'N':
            self.__step_1()
        if self.cal_type == 'NS':
            self.__step_2()




        super(DP011_N_Job, self).do_execute()

    def __step_1(self):
        # df7 = DP011MJob__df7(db_conn_dbprod7=self.db_conn_dbprod7, db_conn_rds=self.db_conn_rds,
        #                      wce_org=self.wce_org,
        #                      data_type=self.data_type,
        #                      account=self.account,
        #                      cost_center_org=self.cost_center_org,
        #                      unit=self.unit,
        #                      account_period_start=self.account_period_start,
        #                      account_period_end=self.account_period_end)
        df7 = self.df7



        sql = " SELECT A.YEAR||A.MONTH AS YMON,A.ACCOUNT,A.COST_CENTER,A.PRODUCT_CODE,A.WCE as B_WCE, " \
              " CASE WHEN LEFT(A.WCE,1) IN ('2','3','7','8') THEN SUM(COALESCE(ACT_AMOUNT_AC_1,0))+SUM(COALESCE(RCCL_VRNC_AMOUNT,0))" \
              " ELSE SUM(COALESCE(ACT_N,0)) END AS B_ACT_N,SUM(COALESCE(ACT_AMOUNT_AC_1,0))+SUM(COALESCE(RCCL_VRNC_AMOUNT,0)) AS B_AMT" \
              " FROM BGRAGGCB.TACACM7 AS A" \
              " WHERE A.ACCOUNT = '%s'" \
              " AND A.YEAR||A.MONTH=substr(replace(cast(cast(to_date(LEFT('%s',6),'yyyymm')-1 months as char(10)) as date) ,'-', ''),1,6)" \
              " GROUP BY  " \
              " A.YEAR||A.MONTH,A.ACCOUNT,A.COST_CENTER,A.PRODUCT_CODE,A.WCE" % (self.account, self.account_period_end)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df.empty is False
        if success is False:
            return
        df.columns = df.columns.str.upper()
        #self.logger.info(df)

        df1 = df

        sql = " SELECT A.YEAR||A.MONTH AS YMON,A.ACCOUNT,A.COST_CENTER,A.PRODUCT_CODE,SUM(COALESCE(EARN_N,0)) AS C_EARN_N " \
              " FROM BGRAGGCB.TACACM6 AS A" \
              " WHERE A.ACCOUNT = '%s'" \
              " AND A.YEAR||A.MONTH=substr(replace(cast(cast(to_date(LEFT('%s',6),'yyyymm')-1 months as char(10)) as date) ,'-', ''),1,6)" \
              " GROUP BY  " \
              " A.YEAR||A.MONTH,A.ACCOUNT,A.COST_CENTER,A.PRODUCT_CODE" % (self.account, self.account_period_end)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df.empty is False
        if success is False:
            return
        df.columns = df.columns.str.upper()
        #self.logger.info(df)

        df2 = df
        df3 = pd.merge(df1, df2, on=['ACCOUNT', 'COST_CENTER', 'PRODUCT_CODE', 'YMON'], how='left')
        def __cal_UNIT_CONSUME(x):
            if x.C_EARN_N == 0:
                rst = 0.000000
            if x.C_EARN_N != 0 and x.B_ACT_N != 0:
                rst = x.B_ACT_N/x.C_EARN_N
            if x.C_EARN_N != 0 and x.B_ACT_N == 0:
                rst = x.B_AMT/x.C_EARN_N

            return rst

        df3['UNIT_CONSUME'] = df3.apply(lambda x: __cal_UNIT_CONSUME(x), axis=1)
        df3_new = df3[df3['B_WCE'] == self.wce_org]

        # df7 merge df2 left on COST_CENTER,ACCOUNT,DATA_TYPE,WORK_DATE 得到df3
        df4 = pd.merge(df7, df3_new, on=['COST_CENTER', 'ACCOUNT', 'PRODUCT_CODE'], how='left')



        def __cal_ACT_N(x):
            rst = 0
            if x.UNIT_CONSUME == 0:
                rst = 0
            else:
                rst = x.UNIT_CONSUME * x.ACT_WT
            return rst

        df4['ACT_N'] = df4.apply(lambda x: __cal_ACT_N(x), axis=1)

        def __cal_CONSUME_N(x):
            rst = 0
            if x.UNIT_CONSUME == 0:
                rst = 0
            else:
                rst = x.UNIT_CONSUME * x.ACT_WT
            return rst

        df4['CONSUME_N'] = df4.apply(lambda x: __cal_CONSUME_N(x), axis=1)
        df4.drop(['UNIT_CONSUME'], axis=1, inplace=True)
        df4.drop(['YMON'], axis=1, inplace=True)
        df4.drop(['B_ACT_N'], axis=1, inplace=True)
        df4.drop(['B_AMT'], axis=1, inplace=True)
        df4.drop(['C_EARN_N'], axis=1, inplace=True)
        df4.drop(['B_WCE'], axis=1, inplace=True)
        df4.drop(['REC_ID'], axis=1, inplace=True)
        df4['REC_CREATOR'] = df4['REC_REVISOR']
        df4['REC_CREATE_TIME'] = df4['REC_REVISOR_TIME']
        df4.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df4.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df4.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df4.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df4.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df4.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df4.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df4.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df4.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df4.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df4.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df4.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df4.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df4.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df4.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df4.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df4.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df4.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        #df4.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df4.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
        df4.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
        df4.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
        df4.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
        df4.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df4.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df4.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df4.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df4.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df4.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df4.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df4.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df4.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df4.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df4.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)

        # 将df3插入到BGRAGGCB.SU_AJBG_DP0101
        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df4,
                       p_max_times=5).redo()

    def __step_2(self):
        sql = " SELECT " \
              " REC_ID," \
              " ACCT AS ACCOUNT," \
              " DEPARTMENT_CODE AS FACTORY," \
              " UNIT_CODE AS UNIT," \
              " CLASS AS TEAM," \
              " SHIFT," \
              " COST_CENTER," \
              " PRODUCE_TIME AS WORK_TIME," \
              " PRODUCE_START_TIME AS PROCESS_START_TIME," \
              " PRODUCE_END_TIME AS PROCESS_END_TIME," \
              " BYPRODUCT_CODE AS PRODUCT_CODE," \
              " STEELNO AS ST_NO," \
              " INPUT_BYPRODUCT_CODE as IN_PRODUCT_CODE," \
              " PROD_COILNO as MAT_NO," \
              " ENTRY_COILNO AS IN_MAT_NO," \
              " APPTHROWAIMODE AS APP_THROW_AI_MODE," \
              " ANNEAL_CURVE AS DESIGN_ANNEAL_DIAGRAM_CODE," \
              " TRIM_FLAG," \
              " MAT_ACT_WIDTH," \
              " MAT_ACT_THICK," \
              " ENTRY_MAT_WIDTH AS IN_MAT_WIDTH," \
              " ENTRY_MAT_THICK AS IN_MAT_THICK," \
              " PLAN_NO," \
              " TRIMMING_AMT AS TRIM_WIDTH," \
              " ENTRY_MAT_INDIA AS IN_MAT_INNER_DIA," \
              " CUST_ORDER_NO," \
              " PICKLING_TRIMMING_FLAG AS PICKL_TRIM_FLAG," \
              " SORT_GRADE_CODE," \
              " COATING_TYPE AS LAYER_TYPE," \
              " MAT_ACT_LEN," \
              " MAT_ACT_AREA," \
              " TOP_COATING_WT as TOP_COAT_WT," \
              " BOT_COATING_WT as BOT_COAT_WT," \
              " COALESCE(OUTPUT_WT,0) AS WT," \
              " ACT_OUTPUT_WT AS ACT_WT," \
              " INPUT_WT AS IN_WT," \
              " ACT_INPUT_WT AS ACT_IN_WT," \
              " COALESCE(A.ACT_INPUT_WT,0) AS CONSUME_N," \
              " INPUT_BYPRODUCT_CODE AS CONSUME_ITEM," \
              " '主原料' AS CONSUME_DESC," \
              " 'T' AS CONSUME_UNIT," \
              " '%s' AS DATA_TYPE," \
              " 'BGRAGGCB' AS REC_REVISOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
              " 'BGRAGGCB' AS REC_CREATOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
              " PRODUCE_NICK_FLAG AS LAS_NOTCH_FLAG" \
              " FROM " \
              " BGTARAS1.T_ADS_FACT_SICB_DP0102" \
              " WHERE 1=1 " \
              " AND ACCT = '%s' " \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE='%s' " \
              " AND DATA_TYPE = '%s'" \
              " AND LEFT(PRODUCE_TIME,14) >='%s'" \
              " AND LEFT(PRODUCE_TIME,14) <'%s'" % (self.data_type,
                                                    self.account,
                                                    self.cost_center_org,
                                                    self.unit,
                                                    self.data_type,
                                                    self.account_period_start,
                                                    self.account_period_end)

        self.logger.info(sql)
        df10 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df10.empty is False
        if success is False:
            return
        df10.columns = df10.columns.str.upper()
        #self.logger.info(df10)

        sql = " SELECT WCE,DEVO_PRODUCT_CODE as IN_PRODUCT_CODE " \
              " FROM BGRAGGCB.TACACTY" \
              " WHERE ACCOUNT = '%s'"  % (self.account)
        self.logger.info(sql)
        df11 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df11.empty is False
        if success is False:
            return
        df11.columns = df11.columns.str.upper()
        #self.logger.info(df11)
        df12 = pd.merge(df10, df11, on=['IN_PRODUCT_CODE'], how='left')

        sql = " SELECT A.ACCOUNT,A.COST_CENTER,A.WCE, " \
              " CASE WHEN LEFT(A.WCE,1) IN ('2','3','7','8') THEN SUM(COALESCE(ACT_AMOUNT_AC_1,0))+SUM(COALESCE(RCCL_VRNC_AMOUNT,0))" \
              " ELSE SUM(COALESCE(ACT_N,0)) END AS D_ACT_N" \
              " FROM BGRAGGCB.TACACM7 AS A" \
              " WHERE A.ACCOUNT = '%s'" \
              " AND A.YEAR||A.MONTH=substr(replace(cast(cast(to_date(LEFT('%s',6),'yyyymm')-1 months as char(10)) as date) ,'-', ''),1,6)" \
              " GROUP BY  " \
              " A.YEAR||A.MONTH,A.ACCOUNT,A.COST_CENTER,A.WCE" % (self.account, self.account_period_end)
        self.logger.info(sql)
        df13 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        success = df13.empty is False
        if success is False:
            return
        df13.columns = df13.columns.str.upper()
        df14 = pd.merge(df12, df13, on=['ACCOUNT', 'COST_CENTER', 'WCE'], how='left')

        sql = " SELECT A.ACCT as ACCOUNT,A.COST_CENTER,A.COST_SUBJECT as WCE , " \
              " SUM(COALESCE(A.COST_SUBJECT_ON_AMT,0)) AS E_ACT_N " \
              " FROM BGTARAS1.T_ADS_FACT_SICB_DP0103 A" \
              " WHERE LEFT(A.PRODUCE_TIME,6) =LEFT('%s',6)" \
              " AND A.COST_CENTER = '%s'" \
              " AND A.DATA_TYPE = 'M'" \
              " GROUP BY A.ACCT,A.COST_CENTER,A.COST_SUBJECT" % (self.account_period_end, self.cost_center_org)
        self.logger.info(sql)
        df15 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df15.empty is False
        if success is False:
            df16 = df14
            df16['E_ACT_N'] = 0
        else:
            df15.columns = df15.columns.str.upper()
            df16 = pd.merge(df14, df15, on=['ACCOUNT', 'COST_CENTER', 'WCE'], how='left')



        def __cal_ACT_N_2(x):
            rst = 0
            if x.E_ACT_N == 0:
                rst = (x.D_ACT_N - x.E_ACT_N) * (x.ACT_IN_WT/1) + x.ACT_IN_WT
            else:
                rst = (x.D_ACT_N - x.E_ACT_N) * (x.ACT_IN_WT/x.E_ACT_N) + x.ACT_IN_WT
            return rst

        df16['ACT_N'] = df16.apply(lambda x: __cal_ACT_N_2(x), axis=1)
        df16.drop(['D_ACT_N'], axis=1, inplace=True)
        df16.drop(['E_ACT_N'], axis=1, inplace=True)
        df16.drop(['REC_ID'], axis=1, inplace=True)
        df16['REC_CREATOR'] = df16['REC_REVISOR']
        df16['REC_CREATE_TIME'] = df16['REC_REVISOR_TIME']
        df16.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df16.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df16.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df16.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df16.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df16.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df16.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df16.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df16.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df16.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df16.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df16.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df16.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df16.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df16.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df16.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df16.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df16.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        #df16.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df16.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
        df16.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
        df16.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
        df16.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
        df16.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df16.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df16.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df16.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df16.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df16.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df16.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df16.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df16.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df16.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df16.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)

        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df16,
                       p_max_times=5).redo()






